/*DONE* Table [dbo].[<CompanyName>$Vendor$437dbf0e-84ff-417a-965d-ed2bb9650972]  1/2  Script Date: 30.7.2020 - JI ******/
/*Add prestaging table for all companies in LSInsight$Companies table*/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DECLARE @CreateTable as VARCHAR(MAX)
DECLARE @Company as VARCHAR(100)
DECLARE @CompanyCounter as INT
DECLARE @i as INT

SET @i = 0
SET @CompanyCounter = (SELECT MAX([Company]) FROM LSInsight$Companies)

WHILE @i <= @CompanyCounter 
BEGIN
SET @Company = (SELECT LSInsight$Companies.[CompanyPrefix] from dbo.LSInsight$Companies WHERE [Company] = @i)

IF @Company IS NOT NULL
BEGIN

DECLARE @DropTable as VARCHAR(MAX)
SET @DropTable = 'DROP TABLE IF EXISTS [dbo].['+@Company+'$Vendor$437dbf0e-84ff-417a-965d-ed2bb9650972]'
EXEC(@DropTable)

SET @CreateTable = 'CREATE TABLE [dbo].['+@Company+'$Vendor$437dbf0e-84ff-417a-965d-ed2bb9650972](
	[timestamp] [timestamp] NOT NULL,
	[No_] [nvarchar](20) NOT NULL,
	[Name] [nvarchar](100) NOT NULL,
	[Search Name] [nvarchar](100) NOT NULL,
	[Name 2] [nvarchar](50) NOT NULL,
	[Address] [nvarchar](100) NOT NULL,
	[Address 2] [nvarchar](50) NOT NULL,
	[City] [nvarchar](30) NOT NULL,
	[Contact] [nvarchar](100) NOT NULL,
	[Phone No_] [nvarchar](30) NOT NULL,
	[Telex No_] [nvarchar](20) NOT NULL,
	[Our Account No_] [nvarchar](20) NOT NULL,
	[Territory Code] [nvarchar](10) NOT NULL,
	[Global Dimension 1 Code] [nvarchar](20) NOT NULL,
	[Global Dimension 2 Code] [nvarchar](20) NOT NULL,
	[Budgeted Amount] [decimal](38, 20) NOT NULL,
	[Vendor Posting Group] [nvarchar](20) NOT NULL,
	[Currency Code] [nvarchar](10) NOT NULL,
	[Language Code] [nvarchar](10) NOT NULL,
	[Statistics Group] [int] NOT NULL,
	[Payment Terms Code] [nvarchar](10) NOT NULL,
	[Fin_ Charge Terms Code] [nvarchar](10) NOT NULL,
	[Purchaser Code] [nvarchar](20) NOT NULL,
	[Shipment Method Code] [nvarchar](10) NOT NULL,
	[Shipping Agent Code] [nvarchar](10) NOT NULL,
	[Invoice Disc_ Code] [nvarchar](20) NOT NULL,
	[Country_Region Code] [nvarchar](10) NOT NULL,
	[Blocked] [int] NOT NULL,
	[Pay-to Vendor No_] [nvarchar](20) NOT NULL,
	[Priority] [int] NOT NULL,
	[Payment Method Code] [nvarchar](10) NOT NULL,
	[Last Modified Date Time] [datetime] NOT NULL,
	[Last Date Modified] [datetime] NOT NULL,
	[Application Method] [int] NOT NULL,
	[Prices Including VAT] [tinyint] NOT NULL,
	[Fax No_] [nvarchar](30) NOT NULL,
	[Telex Answer Back] [nvarchar](20) NOT NULL,
	[VAT Registration No_] [nvarchar](20) NOT NULL,
	[Gen_ Bus_ Posting Group] [nvarchar](20) NOT NULL,
	[Picture] [image] NULL,
	[GLN] [nvarchar](13) NOT NULL,
	[Post Code] [nvarchar](20) NOT NULL,
	[County] [nvarchar](30) NOT NULL,
	[E-Mail] [nvarchar](80) NOT NULL,
	[Home Page] [nvarchar](80) NOT NULL,
	[No_ Series] [nvarchar](20) NOT NULL,
	[Tax Area Code] [nvarchar](20) NOT NULL,
	[Tax Liable] [tinyint] NOT NULL,
	[VAT Bus_ Posting Group] [nvarchar](20) NOT NULL,
	[Block Payment Tolerance] [tinyint] NOT NULL,
	[IC Partner Code] [nvarchar](20) NOT NULL,
	[Prepayment _] [decimal](38, 20) NOT NULL,
	[Partner Type] [int] NOT NULL,
	[Image] [uniqueidentifier] NOT NULL,
	[Privacy Blocked] [tinyint] NOT NULL,
	[Disable Search by Name] [tinyint] NOT NULL,
	[Creditor No_] [nvarchar](20) NOT NULL,
	[Preferred Bank Account Code] [nvarchar](20) NOT NULL,
	[Cash Flow Payment Terms Code] [nvarchar](10) NOT NULL,
	[Primary Contact No_] [nvarchar](20) NOT NULL,
	[Responsibility Center] [nvarchar](10) NOT NULL,
	[Location Code] [nvarchar](10) NOT NULL,
	[Lead Time Calculation] [varchar](32) NOT NULL,
	[Base Calendar Code] [nvarchar](10) NOT NULL,
	[Document Sending Profile] [nvarchar](20) NOT NULL,
	[Validate EU Vat Reg_ No_] [tinyint] NOT NULL,
	[Id] [uniqueidentifier] NOT NULL,
	[Currency Id] [uniqueidentifier] NOT NULL,
	[Payment Terms Id] [uniqueidentifier] NOT NULL,
	[Payment Method Id] [uniqueidentifier] NOT NULL,
	[$systemId] [uniqueidentifier] NOT NULL,
	[Price Calculation Method] [int] NOT NULL,
	[Over-Receipt Code] [nvarchar](20) NOT NULL,
 CONSTRAINT ['+@Company+'$Vendor$437dbf0e-84ff-417a-965d-ed2bb9650972$Key1] PRIMARY KEY CLUSTERED 
(
	[No_] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT ['+@Company+'$Vendor$437dbf0e-84ff-417a-965d-ed2bb9650972$$systemId] UNIQUE NONCLUSTERED 
(
	[$systemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'

EXEC(@CreateTable)

END
SET @i = @i+1
END